<?php

declare(strict_types=1);

namespace App\Order\Service;

use App\Activity\Service\ActivityService;
use App\Common\Service\BaseService;
use App\Common\Constants\Stakeholder;
use App\Order\Model\OrderModel;
use App\Resource\Service\GoodsService;
use App\Resource\Service\ShopService;
use App\Third\Service\Log\KzRefundLogService;
use App\Third\Service\Log\WxRefundLogService;
use App\User\Service\MemberService;
use Carbon\Carbon;
use Hyperf\Contract\LengthAwarePaginatorInterface;
use Hyperf\Contract\PaginatorInterface;
use Hyperf\Database\Concerns\BuildsQueries;
use Hyperf\Database\Model\Builder;
use Hyperf\Di\Annotation\Inject;

class OperateService extends BaseService
{
    /**
     * @Inject()
     * @var ShopService
     */
    private $shopService;

    /**
     * @Inject()
     * @var MemberService
     */
    private $memberService;

    /**
     * @Inject()
     * @var ActivityService
     */
    private $activityService;

    /**
     * @Inject()
     * @var OrderGoodsService
     */
    private $orderGoodsService;

    /**
     * @Inject()
     * @var KzRefundLogService
     */
    private $kzRefundLogService;

    /**
     * @Inject()
     * @var WxRefundLogService
     */
    private $wxRefundLogService;

    /**
     * @Inject()
     * @var RefundGoodsService
     */
    private $refundGoodsService;

    /**
     * @Inject()
     * @var GoodsService
     */
    private $goodsService;

    /**
     * 门店分析
     * @param array $params
     * @param int $perpage
     * @return LengthAwarePaginatorInterface|PaginatorInterface
     */
    public function operateShop(array $params = [])
    {
        $shopNameArr = $this->shopService->getShopNameData();
        // 店铺名称获取店铺ID
        if (!empty($params['shop_name'])) {
            $shopIdArr = $this->shopService->getShopIdArrByShopName((string)$params['shop_name']);
            if (empty($shopIdArr)) {
                $shopIdArr = [mt_rand()];
            }
        }
        //门店账号获取店铺ID
        if (!empty($params['shop_account'])) {
            $shop_id = $this->shopService->getShopIdByShopAccount((string)$params['shop_account']);
        }
        // 店群ID返回, 店铺IDs
        if (!empty($params['shop_group_id'])) {
            $shop_ids = array_unique($this->shopService->getShopIdsByShopGroupId((int)$params['shop_group_id']));
        }
        // 排序
        if (!empty($params['sort_field']) && !empty($params['sort_key'])) {
            $sort = ['sort_field' => $params['sort_field'], 'sort_key' => $params['sort_key']];
        }
        $week = OrderModel::query()
            ->selectRaw('SUM(total_price) as total_amount_w,shop_id')
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', [Carbon::now()->startOfWeek(), Carbon::now()->endOfWeek()])
            //->whereBetween('create_at', ['2020-07-20 00:00:00', '2020-7-26 23:59:59'])
            ->groupBy(['shop_id']);
        $month = OrderModel::query()
            ->selectRaw('SUM(total_price) as total_amount_m,shop_id')
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereMonth('create_at', date('m'))
            ->groupBy(['shop_id']);
        $day = OrderModel::query()
            ->selectRaw('COUNT(1) as order_num,SUM(total_price) as total_amount_d,ROUND(AVG(total_price), 2) as unit_price,store_order.shop_id,week.total_amount_w,month.total_amount_m')
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereDate('create_at', date('Y-m-d'))
            ->groupBy(['store_order.shop_id'])
            ->joinSub($week, 'week', function ($join) {
                $join->on('store_order.shop_id', '=', 'week.shop_id');
            })
            ->joinSub($month, 'month', function ($join) {
                $join->on('store_order.shop_id', '=', 'month.shop_id');
            })
            ->when($shopIdArr ?? 0, function ($query, $shopIdArr) {
                return $query->whereIn('store_order.shop_id', $shopIdArr);
            })
            ->when($shop_id ?? 0, function ($query, $shop_id) {
                return $query->where('store_order.shop_id', $shop_id);
            })
            ->when($shop_ids ?? 0, function ($query, $shop_ids) {
                return $query->whereIn('store_order.shop_id', $shop_ids);
            })
            ->when($sort ?? 0, function ($query, $sort) {
                return $query->orderBy($sort['sort_field'], $sort['sort_key']);
            })
            ->when($params['perpage'] ?? 0, function ($query, $perPage) {
                return $query->paginate((int)$perPage);
            }, function ($query) {
                return $query->get();
            });
        foreach ($day as $k => $v) {
            $day[$k]['shop_name'] = $shopNameArr[$v['shop_id']];
        }
        return $day;
    }

    /**
     * 门店分析--->趋势
     * @param string $shop_id
     * @return array
     */
    public function operateShopChart(string $shop_id)
    {
        $res = $this->operateShopChartSql($shop_id);
        foreach ($arr = array_flip(range(1, 24)) as $k => $v) {
            $arr[$k] = 0;
            if (isset($res[$k])) {
                $arr[$k] = $res[$k];
            }
        }
        $new = [];
        foreach ($arr as $k => $v) {
            $new[$k . ':00'] = $v;
        }
        return ['hour' => array_keys($new), 'amount' => array_values($new)];
    }

    // 门店分析----查看趋势
    public function operateShopChartSql(string $shop_id)
    {
        // 每一小时的销售额
        return OrderModel::query()
            ->selectRaw('HOUR(pay_at) as hour, SUM(total_price) as amount')
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID],
                ['shop_id', '=', $shop_id]
            ])
            ->whereBetween('pay_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])
            ->groupBy(['hour'])
            ->orderBy('hour')
            ->pluck('amount', 'hour')
            ->toArray();
    }

    /**
     * 商品分析--折线图
     * @param array $params
     *
     * @return array
     */
    public function operateGoodsChart(array $params = [])
    {
        $params = $this->conditionParam($params);

        $skuSaleInfo = $this->orderGoodsService->salePty($params);//销量
        $refundQtyInfo = $this->refundGoodsService->refundGoodsNum($params);  //退款数量
        $refundAmtInfo = $this->refundFun($params); // 退款金额
        // 基准折线图
        $trendList = $this->trendList($params);
        //基准商品金额
        $paidAmtInfo = OrderModel::query()
            ->selectRaw('SUM(goods_price) as goods_amount')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->first()
            ->toArray();

        // 对比商品金额
        $contrast = OrderModel::query()
            ->selectRaw('SUM(goods_price) as goods_amount')
            ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order_source', $params['order_source']);
            })
            ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order_type', $params['order_type']);
            })
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->first()
            ->toArray();
        $trend = $this->coefficientsAndTrends((string)$paidAmtInfo['goods_amount'], (string)$contrast['goods_amount']);
        if ($params['flag'] == 1) {
            $contrastTimeStr = $params['contrasttime'][0] . "至" . substr($params['contrasttime'][1], 0, 10);
            $dateStr = $params['date'][0] . "至" . substr($params['date'][1], 0, 10);
        } else {
            $contrastTimeStr = $params['contrasttime'][0] . "至" . substr($params['contrasttime'][1], 0, 7);
            $dateStr = $params['date'][0] . "至" . substr($params['date'][1], 0, 7);
        }

        return [
            'contrastTimeStr' => $contrastTimeStr,
            'dateStr' => $dateStr,
            'paidAmtInfo' =>
                [
                    'amt' => $paidAmtInfo['goods_amount'] ?? 0,
                    'changeFlag' => $trend['changeFlag'],
                    'ratio' => $trend['ratio']
                ],
            'paidQtyInfo' => $skuSaleInfo,
            'refundQtyInfo' => $refundQtyInfo,
            'refundAmtInfo' => $refundAmtInfo,
            'trendList' => $trendList['trendList'],
            'contrastTrendList' => $trendList['contrastTrendList']
        ];
    }

    // 折线图
    private function refundFun(array $params)
    {
        // 客至退款
        $kzRefund = $this->kzRefundLogService->refundAmtInfo($params);
        // 吾享退款
        $wxRefund = $this->wxRefundLogService->refundAmtInfo($params);

        $paidRefundAmt = bcadd((string)$kzRefund['basic'], (string)$wxRefund['basic'], 2); // 退款总额
        $contrastRefundAmt = bcadd((string)$kzRefund['contrast'], (string)$wxRefund['contrast'], 2); // 对比退款总额

        $trend = $this->coefficientsAndTrends((string)$paidRefundAmt, (string)$contrastRefundAmt);

        return ['amt' => $paidRefundAmt ?? 0, 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];
    }

    // 折线图
    private function trendList(array $params)
    {

        if ($params['flag'] == 1) {
            $paidDate = $this->prDates($params['date'][0], $params['date'][1]);
            $contrastDate = $this->prDates($params['contrasttime'][0], $params['contrasttime'][1]);
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,SUM(goods_price) as goods_amount";
        } else {
            $paidDate = $this->prMonths($params['date'][0], $params['date'][1]);
            $contrastDate = $this->prMonths($params['contrasttime'][0], $params['contrasttime'][1]);
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,SUM(goods_price) as goods_amount";
        }
        $res = parallel([
            'saleQty' => function () use ($params) {
                return $this->orderGoodsService->dateGroupSaleQty($params);
            },
            'refundQty' => function () use ($params) {
                return $this->refundGoodsService->refundGoodsNumByDateRange($params);
            },
            'kzr' => function () use ($params) {
                return $this->kzRefundLogService->refundAmtInfoByDateRange($params);
            },
            'wxr' => function () use ($params) {
                return $this->wxRefundLogService->refundAmtInfoByDateRange($params);
            },
            'paidList' => function () use ($params, $selectRaw) {
                // 基准
                return OrderModel::query()
                    ->selectRaw($selectRaw)
                    ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                        return $query->where('order_source', $params['order_source']);
                    })
                    ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                        return $query->where('order_type', $params['order_type']);
                    })
                    ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                        return $query->where('shop_id', $shop_id);
                    })
                    ->where([
                        ['is_pay', '=', Stakeholder::ORDER_PAID]
                    ])
                    ->whereBetween('create_at', $params['date'])
                    ->groupBy(['date'])
                    ->get()
                    ->toArray();
            },
            'contrastList' => function () use ($params, $selectRaw) {
                // 对比
                return OrderModel::query()
                    ->selectRaw($selectRaw)
                    ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                        return $query->where('order_source', $params['order_source']);
                    })
                    ->when(is_numeric($params['order_type']), function ($query) use ($params) {
                        return $query->where('order_type', $params['order_type']);
                    })
                    ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                        return $query->where('shop_id', $shop_id);
                    })
                    ->where([
                        ['is_pay', '=', Stakeholder::ORDER_PAID]
                    ])
                    ->whereBetween('create_at', $params['contrasttime'])
                    ->groupBy(['date'])
                    ->get()
                    ->toArray();
            }
        ]);
        $cgoods_amount = array_column($res['contrastList'] ?? [], 'goods_amount', 'date');
        $ctotalQty = array_column($res['saleQty']['contrast'] ?? [], 'sale_qty', 'date');
        $ctotalRefundQty = array_column($res['refundQty']['contrast'] ?? [], 'refund_number', 'date');
        $crefund_balance_money = array_column($res['kzr']['contrast'] ?? [], 'refund_balance_money', 'date');
        $crefund_wx_money = array_column($res['wxr']['contrast'] ?? [], 'refund_wx_money', 'date');

        $bgoods_amount = array_column($res['paidList'] ?? [], 'goods_amount', 'date');
        $btotalQty = array_column($res['saleQty']['basic'] ?? [], 'sale_qty', 'date');
        $btotalRefundQty = array_column($res['refundQty']['basic'] ?? [], 'refund_number', 'date');
        $brefund_balance_money = array_column($res['kzr']['basic'] ?? [], 'refund_balance_money', 'date');
        $brefund_wx_money = array_column($res['wxr']['basic'] ?? [], 'refund_wx_money', 'date');
        $list = [];
        $cList = [];
        foreach ($contrastDate as $kk => $vv) {
            if ($params['flag'] == 1) {
                $cList[$kk]['date'] = substr($vv, -5);
            } else {
                $cList[$kk]['date'] = $vv;
            }
            $cList[$kk]['goods_amount'] = $cgoods_amount[$vv] ?? 0;// 商品金额
            $cList[$kk]['totalQty'] = $ctotalQty[$vv] ?? 0;// 销售数量
            $cList[$kk]['totalRefundQty'] = $ctotalRefundQty[$vv] ?? 0;// 退款数量
            $cList[$kk]['kz'] = $crefund_balance_money[$vv] ?? 0;
            $cList[$kk]['wx'] = $crefund_wx_money[$vv] ?? 0;
            $cList[$kk]['totalRefundAmt'] = bcadd((string)$cList[$kk]['kz'], (string)$cList[$kk]['wx'], 2); // 退款金额
        }
        foreach ($paidDate as $kk => $vv) {
            if ($params['flag'] == 1) {
                $list[$kk]['date'] = substr($vv, -5);
            } else {
                $list[$kk]['date'] = $vv;
            }
            $list[$kk]['goods_amount'] = $bgoods_amount[$vv] ?? 0;// 商品金额
            $list[$kk]['totalQty'] = $btotalQty[$vv] ?? 0;// 销售数量
            $list[$kk]['totalRefundQty'] = $btotalRefundQty[$vv] ?? 0;// 退款数量
            $list[$kk]['kz'] = $brefund_balance_money[$vv] ?? 0;
            $list[$kk]['wx'] = $brefund_wx_money[$vv] ?? 0;
            $list[$kk]['totalRefundAmt'] = bcadd((string)$list[$kk]['kz'], (string)$list[$kk]['wx'], 2); // 退款金额
        }
        return ['trendList' => $list, 'contrastTrendList' => $cList];
    }

    /**
     * 运营分析---商品销售分析
     * @param array $params
     *
     * @return array
     */
    public function operateGoodsSale(array $params = [])
    {
        $params = $this->conditionParam($params);
        if (!empty($params['shop_id'])) {
            // 门店名称  //店群名称
            $shopInfo = $this->shopService->getInfoById((int)$params['shop_id']);
            // 门店总销售额
            $totalAmt = OrderModel::query()->where(
                [
                    ['shop_id', '=', $params['shop_id']],
                    ['is_pay', '=', Stakeholder::ORDER_PAID]
                ]
            )->when(is_numeric($params['order_source']), function ($query) use ($params) {
                return $query->where('order_source', $params['order_source']);
            })->when(is_numeric($params['order_type']), function ($query) use ($params) {
                return $query->where('order_type', $params['order_type']);
            })->whereBetween('pay_at', $params['date'])->sum('goods_price');
        } else {
            // 全部销售额
            $totalAmt = OrderModel::query()
                ->where('is_pay', Stakeholder::ORDER_PAID)
                ->when(is_numeric($params['order_source']), function ($query) use ($params) {
                    return $query->where('order_source', $params['order_source']);
                })->when(is_numeric($params['order_type']), function ($query) use ($params) {
                    return $query->where('order_type', $params['order_type']);
                })->whereBetween('pay_at', $params['date'])->sum('goods_price');
        }

        $list = $this->orderGoodsService->goodsSaleAnalysisList($params);

        foreach ($list as $k => $v) {
            $list[$k]['unit'] = explode('/', $v['goods_spec'])[1]; //单位
            $list[$k]['integral'] = 0;     //积分抵扣
            $list[$k]['goods_discount_amount'] = $v['deductMoney']; //商品优惠金额
            $list[$k]['goods_refund_num'] = bcadd((string)$v['memb_ref_num'], (string)$v['shop_ref_num']); //商品退款数量
            $list[$k]['goods_refund_amt'] = bcadd((string)$v['memb_ref_amt'], (string)$v['shop_ref_amt'], 2); //商品退款金额
            $list[$k]['shop_name'] = $shopInfo['data']['shop_name'] ?? '全部门店';
            $list[$k]['shop_group'] = $shopInfo['data']['group_name'] ?? '--';
            $list[$k]['sale_scale'] = bcmul((string)bcdiv((string)$v['goods_amt'], (string)$totalAmt, 6), '100', 4) . '%';
            $list[$k]['original_amt'] = bcadd((string)$v['goods_amt'], (string)$v['deductMoney'], 2);       //商品原金额
//            $list[$k]['receivable_amt'] = bcsub((string)$list[$k]['original_amt'], (string)$list[$k]['goods_refund_amt'], 2);       //应收金额
            $list[$k]['receivable_amt'] = $list[$k]['original_amt'];       //应收金额
            $list[$k]['actually_paid'] = bcsub((string)$v['goods_amt'], (string)$list[$k]['goods_refund_amt'], 2);       //实付金额
            unset($v['memb_ref_num'], $v['shop_ref_num'], $v['memb_ref_amt'], $v['shop_ref_amt']);
        }
        return $list;
    }

    /**
     * 运营分析--分类销售明细
     * @param array $params
     *
     * @return array
     */
    public function operateCateChart(array $params)
    {
        $params = $this->conditionParam($params);
        return $this->orderGoodsService->cateSalesDetails($params);
    }

    /**
     * 运营分析---用户概览
     * @param array $params
     *
     * @return array
     */
    public function operateMemberChart(array $params)
    {
        $params = $this->conditionParam($params);
        $result = parallel([
            'memberNums' => function () use ($params) {
                // 新增会员数
                $newMemberNums = $this->memberService->newMemberCount($params);
                // 消费会员数
                $paidMemberNums = $this->paidMemberNums($params);
                // 新增消费会员数
                $newPaidMemberNums = $this->newPaidMemberNums($params, $paidMemberNums['combination'], $newMemberNums['combination']);
                return ['newMemberNums' => $newMemberNums, 'paidMemberNums' => $paidMemberNums, 'newPaidMemberNums' => $newPaidMemberNums];
            },
            'memberOrderCount' => function () use ($params) {
                // 会员订单数
                return $this->memberOrderCount($params);
            },
            'memberOrderPaid' => function () use ($params) {
                // 会员订单金额
                return $this->memberOrderPaid($params);
            },
            'memberAvgOrderPaid' => function () use ($params) {
                // 会员笔单价
                return $this->memberAvgOrderPaid($params);
            },
        ]);

        // 折线图
        $lineChart = $this->memberlineChart($params, $result['memberNums']['paidMemberNums']['combination'], $result['memberNums']['newMemberNums']['combination']);

        if ($params['flag'] == 1) {
            $contrastTimeStr = $params['contrasttime'][0] . "至" . substr($params['contrasttime'][1], 0, 10);
            $dateStr = $params['date'][0] . "至" . substr($params['date'][1], 0, 10);
        } else {
            $contrastTimeStr = $params['contrasttime'][0] . "至" . substr($params['contrasttime'][1], 0, 7);
            $dateStr = $params['date'][0] . "至" . substr($params['date'][1], 0, 7);
        }
        return [
            'contrastTimeStr' => $contrastTimeStr,
            'dateStr' => $dateStr,
            'newMemberNums' => $result['memberNums']['newMemberNums']['data'],  //新增会员数
            'paidMemberNums' => $result['memberNums']['paidMemberNums']['data'], //消费会员数
            'memberOrderCount' => $result['memberOrderCount'], //会员订单数
            'memberOrderPaid' => $result['memberOrderPaid'], //会员订单金额
            'memberAvgOrderPaid' => $result['memberAvgOrderPaid'], //会员笔单价
            'newPaidMemberNums' => $result['memberNums']['newPaidMemberNums'], //新增消费会员数
            'memberOrderCountPercent' => [//会员订单数占比
                'amt' => '100%',
                'changeFlag' => 0,
                'ratio' => '0%'
            ],
            'memberOrderPaidPercent' => [//会员订单金额占比
                'amt' => '100%',
                'changeFlag' => 0,
                'ratio' => '0%'
            ],
            'trendList' => $lineChart['trendList'],
            'contrastTrendList' => $lineChart['contrastTrendList']
        ];
    }

    // 用户概览---消费会员数
    private function paidMemberNums(array $params)
    {
        $basic = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->distinct()
            ->pluck('mid')
            ->toArray();

        $contrast = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->distinct()
            ->pluck('mid')
            ->toArray();
        $basicCount = count($basic);
        $contrastCount = count($contrast);

        $trend = $this->coefficientsAndTrends((string)$basicCount, (string)$contrastCount);

        return [
            'data' => [
                'amt' => $basicCount,
                'changeFlag' => $trend['changeFlag'],
                'ratio' => $trend['ratio']
            ],
            'combination' => [
                'basic' => $basic,
                'contrast' => $contrast
            ]
        ];
    }

    //用户概览---新增消费会员数
    private function newPaidMemberNums(array $params, array $paidMemberCombination, array $newMemberCombination)
    {
        // 已消费会员
        $basic = $paidMemberCombination['basic'];
        $contrast = $paidMemberCombination['contrast'];

        // 新增会员
        $newBasic = $newMemberCombination['basic'];
        $newContrast = $newMemberCombination['contrast'];

        $basicCount = count(array_intersect($basic, $newBasic));
        $contrastCount = count(array_intersect($contrast, $newContrast));

        $trend = $this->coefficientsAndTrends((string)$basicCount, (string)$contrastCount);

        return ['amt' => $basicCount, 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];

    }

    // 用户概览---会员订单数
    private function memberOrderCount(array $params)
    {
        $basicCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->count();

        $contrastCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->count();

        $trend = $this->coefficientsAndTrends((string)$basicCount, (string)$contrastCount);

        return ['amt' => $basicCount, 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];
    }

    // 用户概览---会员订单金额
    private function memberOrderPaid(array $params)
    {
        $basicCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->sum('total_price');

        $contrastCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->sum('total_price');

        $trend = $this->coefficientsAndTrends((string)$basicCount, (string)$contrastCount);

        return ['amt' => $basicCount, 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];
    }

    //用户概览---会员笔单价
    private function memberAvgOrderPaid(array $params)
    {
        $basicCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->avg('total_price');

        $contrastCount = OrderModel::query()
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->avg('total_price');

        $trend = $this->coefficientsAndTrends((string)$basicCount, (string)$contrastCount);

        return ['amt' => round($basicCount, 2), 'changeFlag' => $trend['changeFlag'], 'ratio' => $trend['ratio']];
    }

    /**
     * 用户概览折线图
     * @param array $params
     * @param array $paidMemberArr
     * @param array $newMemberArr
     * @return array[]
     */
    private function memberlineChart(array $params, array $paidMemberArr, array $newMemberArr)
    {
        if ($params['flag'] == 1) {
            $basicDate = $this->prDates($params['date'][0], $params['date'][1]);
            $contrastDate = $this->prDates($params['contrasttime'][0], $params['contrasttime'][1]);
        } else {
            $basicDate = $this->prMonths($params['date'][0], $params['date'][1]);
            $contrastDate = $this->prMonths($params['contrasttime'][0], $params['contrasttime'][1]);
        }

        $res = parallel([
            'newMemberNumsList' => function () use ($params) {
                // 新增会员数
                return $this->memberService->newMemberCountGroupByDate($params);
            },
            'paidMemberNums' => function () use ($params) {
                // 消费会员数
                return $this->paidMemberNumsGroupByDate($params);
            },
            'memberOrderCount' => function () use ($params) {
                // 会员订单数
                return $this->memberOrderCountGroupByDate($params);
            },
            'memberOrderPaid' => function () use ($params) {
                // 会员订单金额
                return $this->memberOrderPaidGroupByDate($params);
            },
            'memberAvgOrderPaid' => function () use ($params) {
                // 会员笔单价
                return $this->memberAvgOrderPaidGroupByDate($params);
            },
            'newPaidMemberNums' => function () use ($params, $paidMemberArr, $newMemberArr) {
                // 新增消费会员数
                return $this->newPaidMemberNumsGroupByDate($params, $paidMemberArr, $newMemberArr);
            },
        ]);

        $bList = [];
        $cList = [];
        $newMemberNumsc = array_column($res['newMemberNumsList']['contrast'] ?? [], 'newMemberNums', 'date');
        $paidMemberNumsc = array_column($res['paidMemberNums']['contrast'] ?? [], 'paidMemberNums', 'date');
        $memberOrderCountc = array_column($res['memberOrderCount']['contrast'] ?? [], 'memberOrderCount', 'date');
        $memberOrderPaidc = array_column($res['memberOrderPaid']['contrast'] ?? [], 'memberOrderPaid', 'date');
        $memberAvgOrderPaidc = array_column($res['memberAvgOrderPaid']['contrast'] ?? [], 'memberAvgOrderPaid', 'date');
        $newPaidMemberNumsc = array_column($res['newPaidMemberNums']['contrast'] ?? [], 'newPaidMemberNums', 'date');

        $newMemberNumsb = array_column($res['newMemberNumsList']['basic'] ?? [], 'newMemberNums', 'date');
        $paidMemberNumsb = array_column($res['paidMemberNums']['basic'] ?? [], 'paidMemberNums', 'date');
        $memberOrderCountb = array_column($res['memberOrderCount']['basic'] ?? [], 'memberOrderCount', 'date');
        $memberOrderPaidb = array_column($res['memberOrderPaid']['basic'] ?? [], 'memberOrderPaid', 'date');
        $memberAvgOrderPaidb = array_column($res['memberAvgOrderPaid']['basic'] ?? [], 'memberAvgOrderPaid', 'date');
        $newPaidMemberNumsb = array_column($res['newPaidMemberNums']['basic'] ?? [], 'newPaidMemberNums', 'date');
        // 对比折线图
        foreach ($contrastDate as $kk => $vv) {
            if ($params['flag'] == 1) {
                $cList[$kk]['date'] = substr($vv, -5);
            } else {
                $cList[$kk]['date'] = $vv;
            }
            $cList[$kk]['newPaidMemberNums'] = 0;         // 新增消费会员数
            $cList[$kk]['memberOrderCountPercent'] = 100; // 会员订单数占比
            $cList[$kk]['memberOrderPaidPercent'] = 100; // 会员订单金额占比
            $cList[$kk]['newMemberNums'] = $newMemberNumsc[$vv] ?? 0;// 新增会员数
            $cList[$kk]['paidMemberNums'] = $paidMemberNumsc[$vv] ?? 0;// 消费会员数
            $cList[$kk]['memberOrderCount'] = $memberOrderCountc[$vv] ?? 0;// 会员订单数
            $cList[$kk]['memberOrderPaid'] = $memberOrderPaidc[$vv] ?? 0;// 会员订单金额
            $cList[$kk]['memberAvgOrderPaid'] = $memberAvgOrderPaidc[$vv] ?? 0;// 会员笔订单
            $cList[$kk]['newPaidMemberNums'] += $newPaidMemberNumsc[$vv] ?? 0;// 新增消费会员数

        }

        // 基准折线图
        foreach ($basicDate as $kk => $vv) {
            if ($params['flag'] == 1) {
                $bList[$kk]['date'] = substr($vv, -5);
            } else {
                $bList[$kk]['date'] = $vv;
            }

            $bList[$kk]['newPaidMemberNums'] = 0;         // 新增消费会员数
            $bList[$kk]['memberOrderCountPercent'] = 100; // 会员订单数占比
            $bList[$kk]['memberOrderPaidPercent'] = 100; // 会员订单金额占比
            $bList[$kk]['newMemberNums'] = $newMemberNumsb[$vv] ?? 0;// 新增会员数
            $bList[$kk]['paidMemberNums'] = $paidMemberNumsb[$vv] ?? 0;// 消费会员数
            $bList[$kk]['memberOrderCount'] = $memberOrderCountb[$vv] ?? 0;// 会员订单数
            $bList[$kk]['memberOrderPaid'] = $memberOrderPaidb[$vv] ?? 0;// 会员订单金额
            $bList[$kk]['memberAvgOrderPaid'] = $memberAvgOrderPaidb[$vv] ?? 0;// 会员笔订单
            $bList[$kk]['newPaidMemberNums'] += $newPaidMemberNumsb[$vv] ?? 0;// 新增消费会员数

        }

        return ['trendList' => $bList, 'contrastTrendList' => $cList];
    }

    // 用户概览---消费会员数(折线图)
    private function paidMemberNumsGroupByDate(array $params)
    {
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,count(distinct mid) as paidMemberNums";
        } else {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,count(distinct mid) as paidMemberNums";
        }
        $basic = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        $contrast = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        return ['basic' => $basic, 'contrast' => $contrast];
    }

    // 用户概览---会员订单数(折线图)
    private function memberOrderCountGroupByDate(array $params)
    {
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,count(1) as memberOrderCount";
        } else {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,count(1) as memberOrderCount";
        }
        $basic = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        $contrast = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        return ['basic' => $basic, 'contrast' => $contrast];
    }

    // 用户概览---会员订单金额(折线图)
    private function memberOrderPaidGroupByDate(array $params)
    {
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,SUM(total_price) as memberOrderPaid";
        } else {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,SUM(total_price) as memberOrderPaid";
        }
        $basic = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        $contrast = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        return ['basic' => $basic, 'contrast' => $contrast];
    }

    //用户概览---会员笔单价(折线图)
    private function memberAvgOrderPaidGroupByDate(array $params)
    {
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,ROUND(AVG(total_price),2) as memberAvgOrderPaid";
        } else {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,ROUND(AVG(total_price),2) as memberAvgOrderPaid";
        }
        $basic = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        $contrast = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['contrasttime'])
            ->groupBy(['date'])
            ->get()
            ->toArray();

        return ['basic' => $basic, 'contrast' => $contrast];
    }

    //用户概览---新增消费会员数(折线图)
    private function newPaidMemberNumsGroupByDate(array $params, array $paidMemberArr, array $newMemberArr)
    {
        $basicMemberArr = array_intersect($paidMemberArr['basic'], $newMemberArr['basic']);
        $contrastMemberArr = array_intersect($paidMemberArr['contrast'], $newMemberArr['contrast']);
        if ($params['flag'] == 1) {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m-%d') as date,count(distinct mid) as newPaidMemberNums";
        } else {
            $selectRaw = "DATE_FORMAT(create_at, '%Y-%m') as date,count(distinct mid) as newPaidMemberNums";
        }
        // 每一个会员在这个时段中第一次消费的时间
        $basic = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereIn('mid', $basicMemberArr)
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['mid'])
            ->get()
            ->toArray();

        $contrast = OrderModel::query()
            ->selectRaw($selectRaw)
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereIn('mid', $contrastMemberArr)
            ->whereBetween('create_at', $params['contrasttime'])
            ->groupBy(['mid'])
            ->get()
            ->toArray();

        return ['basic' => $basic, 'contrast' => $contrast];
    }

    /**
     * 会员来源渠道
     * @param array $params
     * @return array|int|mixed
     */
    public function operateMemberSourceChart(array $params)
    {
        $params = $this->conditionParam($params);
        return $this->memberService->operateMemberSource($params);
    }

    /**
     * 会员来源门店
     * @param array $params
     * @return LengthAwarePaginatorInterface|PaginatorInterface
     */
    public function operateMemberSource(array $params)
    {
        $params = $this->conditionParam($params);
        // 店铺名称
        $shopNameArr = $this->shopService->getShopNameData();
        $totalMemberCount = $this->memberService->getAllMemberCount(); //所有会员数
        $newMember = $this->memberService->getNewMemberMidArr($params);
        $list = OrderModel::query()
            ->selectRaw("count(distinct mid) as newAddMemberCount,GROUP_CONCAT(distinct mid) as midStr,shop_id")
            ->when($params['shop_id'] ?? 0, function ($query, $shop_id) {
                return $query->where('shop_id', $shop_id);
            })
            ->where([
                ['is_pay', '=', Stakeholder::ORDER_PAID]
            ])
            ->whereBetween('create_at', $params['date'])
            ->groupBy(['shop_id'])
            ->paginate((int)$params['perpage']);
        foreach ($list as $k => $v) {
            $list[$k]['shopName'] = $shopNameArr[$v['shop_id']];
            $list[$k]['newAddMemberCount'] = count(array_intersect($newMember, explode(',', $v['midStr'])));
            if ($list[$k]['newAddMemberCount']) {
                $list[$k]['newMemberPercentage'] = bcmul(bcdiv((string)$list[$k]['newAddMemberCount'], (string)$totalMemberCount, 2), '100') . '%';
            } else {
                $list[$k]['newMemberPercentage'] = '0%';
            }
            $list[$k]['totalMemberCount'] = $totalMemberCount;
            $list[$k]['totalPercentage'] = '100%';
        }
        return $list;
    }

    /**
     * 参数整合
     * @param array $params
     * @return array
     */
    public function conditionParam(array $params)
    {
        // 日交易数据
        if ($params['flag'] == 1) {
            $date_s = trim(substr($params['date'], 0, 10));
            $date_e = trim(substr($params['date'], -10));
            $params['date'] = [$date_s, $date_e . ' 23:59:59'];
            $day = $this->diffBetweenTwoDays($date_s, $date_e);

            $params['contrasttime'] = [$params['contrasttime'], date('Y-m-d 23:59:59', strtotime("+$day day", strtotime($params['contrasttime'])))];
        }
        // 月交易数据
        if ($params['flag'] == 2) {
            $date_s = trim(substr($params['date'], 0, 7));
            $date_e = trim(substr($params['date'], -7));
            $params['date'] = [$date_s, Carbon::parse($date_e)->endOfMonth()->toDateTimeString()];
            $month = $this->diffBetweenMonthNum($date_s, $date_e);
            $contrasttime = trim(substr($params['contrasttime'], 0, 7));
            $params['contrasttime'] = [$contrasttime, Carbon::parse($contrasttime)->addMonths($month)->endOfMonth()->toDateTimeString()];
        }
        return $params;
    }

    /**
     * 运营分析---拼团活动数据分析
     * @param array $params
     * @param array|string[] $field
     * @return BuildsQueries|Builder|mixed
     */

    public function activityAnalysis(array $params, array $field = ['*'])
    {
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        // 日期时间段2020-07-25 - 2020-08-02 // 2020-07-03 14:25:00 - 2020-08-07 14:23:00
        if (!empty($start_time) && strlen(trim($start_time)) > 23) {
            $params['start'] = [trim(substr($start_time, 0, 19)), trim(substr($start_time, -19))];
        }
        if (!empty($start_time) && strlen(trim($start_time)) < 24) {
            $params['start'] = [trim(substr($start_time, 0, 10)), trim(substr($start_time, -10))];
        }
        if (!empty($end_time) && strlen(trim($end_time)) > 23) {
            $params['end'] = [trim(substr($end_time, 0, 19)), trim(substr($end_time, -19))];
        }
        if (!empty($end_time) && strlen(trim($end_time)) < 24) {
            $params['end'] = [trim(substr($end_time, 0, 10)), trim(substr($end_time, -10))];
        }
        return $this->activityService->activityGroupList($params, $field);
    }


}
